from flask import request, jsonify
from sqlalchemy import func
from sqlalchemy import text
from sqlalchemy.exc import IntegrityError

from tzq.database_compare_system import dcs_bp
from tzq.database_compare_system.models.dcs_task_models import CompareTask
from tzq.extensions import db


# 获取数据接口 (Layui表格格式)
@dcs_bp.route('/api/compareTasks', methods=['GET'])
def get_compare_tasks():
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 10))
    offset = (page - 1) * limit

    # 获取搜索参数
    comp_task_name = request.args.get('task_name', '')
    connect_name = request.args.get('connect_name', '')

    sql = text("""
    select comp_task_id,comp_task_name,db_info_1_id,connect_name1,db_info_2_id,connect_name2,db_info_3_id,connect_name3,db_info_4_id,connect_name4,db_info_5_id,connect_name5,db_info_6_id,connect_name6,db_info_7_id,connect_name7,created_by,creation_date,last_updated_by,last_update_date,comment 
  from (select ARRAY[connect_name1, connect_name2, connect_name3,
                     connect_name4, connect_name5, connect_name6, connect_name7] as a1
              ,t.* 
          from (select t.comp_task_id as comp_task_id
                      ,t.comp_task_name
                      ,t.db_info_1_id ,a1.connect_name as connect_name1
                      ,t.db_info_2_id ,a2.connect_name as connect_name2
                      ,t.db_info_3_id ,a3.connect_name as connect_name3
                      ,t.db_info_4_id ,a4.connect_name as connect_name4
                      ,t.db_info_5_id ,a5.connect_name as connect_name5
                      ,t.db_info_6_id ,a6.connect_name as connect_name6
                      ,t.db_info_7_id ,a7.connect_name as connect_name7
                      ,t.created_by
                      ,to_char(t.creation_date, 'yyyy-mm-dd hh24:mi:ss') AS creation_date
                      ,t.last_updated_by
                      ,to_char(t.last_update_date, 'yyyy-mm-dd hh24:mi:ss') AS last_update_date
                      ,t.comment
                  from dc_comp_task_t t
                  left join dc_database_info_t a1 on a1.db_info_id = t.db_info_1_id
                  left join dc_database_info_t a2 on a2.db_info_id = t.db_info_2_id
                  left join dc_database_info_t a3 on a3.db_info_id = t.db_info_3_id
                  left join dc_database_info_t a4 on a4.db_info_id = t.db_info_4_id
                  left join dc_database_info_t a5 on a5.db_info_id = t.db_info_5_id
                  left join dc_database_info_t a6 on a6.db_info_id = t.db_info_6_id
                  left join dc_database_info_t a7 on a7.db_info_id = t.db_info_7_id) t
       ) q 
 where 1=1
   and comp_task_name ~* :comp_task_name
   and EXISTS (
      SELECT 1
        FROM generate_subscripts(q.a1, 1) AS t(i)
       WHERE q.a1[t.i]  ~* concat('^.*', :connect_name , '.*$'))
order by last_update_date desc
limit :limit OFFSET :offset
    """)

    #
    # # 基础查询
    # query = CompareTask.query
    #
    # # 应用搜索条件
    # if comp_task_name:
    #     query = query.filter(CompareTask.comp_task_name.ilike(f'%{comp_task_name}%'))
    # if connect_name:
    #     # query = query.filter(CompareTask.connect_name.ilike(f'%{connect_name}%'))
    #     print("pass")
    # # 获取排序参数
    # sort_field = request.args.get('field', 'last_update_date')
    # sort_order = request.args.get('order', 'desc')
    #
    # # 构建排序
    # if hasattr(CompareTask, sort_field):
    #     if sort_order == 'asc':
    #         order_by = getattr(CompareTask, sort_field).asc()
    #     else:
    #         order_by = getattr(CompareTask, sort_field).desc()
    #     query = query.order_by(order_by)
    # else:
    #     query = query.order_by(CompareTask.last_update_date.desc())
    #
    # # 分页查询
    # pagination = query.paginate(page=page, per_page=limit, error_out=False)
    #
    # # page = User.query.filter(User.status == 1).order_by(User.id.desc()).paginate(page=1, per_page=10)

    results = db.session.execute(sql, {
        'comp_task_name': comp_task_name,
        'connect_name': connect_name,
        'limit': limit,
        'offset': offset
    }).fetchall()

    tmp = []
    for item in results:
        item = item._asdict()
        tmp.append(item)

    # 查询总数
    v_count_sql = text("""
    select count(*) as sl
  from (select ARRAY[connect_name1, connect_name2, connect_name3,
                     connect_name4, connect_name5, connect_name6, connect_name7] as a1
              ,t.* 
          from (select t.comp_task_id as comp_task_id
                      ,t.comp_task_name
                      ,t.db_info_1_id ,a1.connect_name as connect_name1
                      ,t.db_info_2_id ,a2.connect_name as connect_name2
                      ,t.db_info_3_id ,a3.connect_name as connect_name3
                      ,t.db_info_4_id ,a4.connect_name as connect_name4
                      ,t.db_info_5_id ,a5.connect_name as connect_name5
                      ,t.db_info_6_id ,a6.connect_name as connect_name6
                      ,t.db_info_7_id ,a7.connect_name as connect_name7
                      ,t.created_by
                      ,to_char(t.creation_date, 'yyyy-mm-dd hh24:mi:ss') AS creation_date
                      ,t.last_updated_by
                      ,to_char(t.last_update_date, 'yyyy-mm-dd hh24:mi:ss') AS last_update_date
                      ,t.comment
                  from dc_comp_task_t t
                  left join dc_database_info_t a1 on a1.db_info_id = t.db_info_1_id
                  left join dc_database_info_t a2 on a2.db_info_id = t.db_info_2_id
                  left join dc_database_info_t a3 on a3.db_info_id = t.db_info_3_id
                  left join dc_database_info_t a4 on a4.db_info_id = t.db_info_4_id
                  left join dc_database_info_t a5 on a5.db_info_id = t.db_info_5_id
                  left join dc_database_info_t a6 on a6.db_info_id = t.db_info_6_id
                  left join dc_database_info_t a7 on a7.db_info_id = t.db_info_7_id) t
       ) q 
 where 1=1
   and comp_task_name ~* :comp_task_name
   and EXISTS (
      SELECT 1
        FROM generate_subscripts(q.a1, 1) AS t(i)
       WHERE q.a1[t.i]  ~* concat('^.*', :connect_name , '.*$'))
    """)
    v_count_results = db.session.execute(v_count_sql, {
        'comp_task_name': comp_task_name,
        'connect_name': connect_name
    }).fetchone()
    # print("str(v_count_results)=" + str(v_count_results))
    v_count_results2 = v_count_results._asdict()
    # print("str(v_count_results2)=" + str(v_count_results2))
    v_count_num = int(v_count_results2['sl'])
    # print("str(v_count_results2['sl'])=" + str(int(v_count_results2['sl'])))

    return jsonify({
        'code': 0,
        'msg': '',
        'count': v_count_num,
        'data': tmp
    })


# 添加数据接口
@dcs_bp.route('/api/compareTask', methods=['POST'])
def add_compare_task():
    data = request.get_json()
    # print("data = " + str(data))
    try:
        compareTask = CompareTask(
            comp_task_name=data['comp_task_name'],
            db_info_1_id=int(data['dataSource1']),
            db_info_2_id=int(data['dataSource2']),
            db_info_3_id=int(data['dataSource3']) if data['dataSource3'] != '' else None,
            db_info_4_id=int(data['dataSource4']) if data['dataSource4'] != '' else None,
            db_info_5_id=int(data['dataSource5']) if data['dataSource5'] != '' else None,
            db_info_6_id=int(data['dataSource6']) if data['dataSource6'] != '' else None,
            db_info_7_id=int(data['dataSource7']) if data['dataSource7'] != '' else None,
            # db_info_7_id=int(data['dataSource7']),
            comment=data['comment']
        )
        db.session.add(compareTask)
        db.session.commit()
        return jsonify({"code": 0, "msg": "添加成功"})
    except IntegrityError:
        return jsonify({"code": 1, "msg": "数据已存在"}), 400
    except Exception as e:
        return jsonify({"code": 2, "msg": str(e)}), 500


# 更新数据接口
@dcs_bp.route('/api/compareTask/<int:id>', methods=['PUT'])
def update_compare_task(id):
    compareTask = CompareTask.query.get_or_404(id)
    data = request.get_json()
    # print("data = " + str(data))
    try:
        compareTask.comp_task_name = data['comp_task_name']
        compareTask.db_info_1_id = int(data['dataSource1'])
        compareTask.db_info_2_id = int(data['dataSource2'])

        compareTask.db_info_3_id = int(data['dataSource3']) if data['dataSource3'] != '' else None
        compareTask.db_info_4_id = int(data['dataSource4']) if data['dataSource4'] != '' else None
        compareTask.db_info_5_id = int(data['dataSource5']) if data['dataSource5'] != '' else None
        compareTask.db_info_6_id = int(data['dataSource6']) if data['dataSource6'] != '' else None
        compareTask.db_info_7_id = int(data['dataSource7']) if data['dataSource7'] != '' else None

        compareTask.comment = data['comment']
        compareTask.last_update_date = func.now()
        db.session.commit()
        return jsonify({"code": 0, "msg": "更新成功"})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500


# 删除数据接口
@dcs_bp.route('/api/compareTask/<int:id>', methods=['DELETE'])
def delete_compare_task(id):
    compareTask = CompareTask.query.get_or_404(id)
    try:
        db.session.delete(compareTask)
        db.session.commit()
        return jsonify({"code": 0, "msg": "删除成功"})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500
